# -*- coding:utf8 -*-
#encoding = utf-8

'''
	1. 先统计京东

	2. 苏宁
	3. 淘宝天猫

	4. 增加判断功能，如果存在就不加入的功能

	5. 新加一波数据跑一次


'''





import mandb

dbgo = mandb.DB()
dbgo.test()
#print(dbgo.db_connect())





#整体平均销量值
def all_cod_sales_avg(fenlei_data):
	man_db = mandb.DB()
	#京东平均销量
	jd_cod_sales_avg = "SELECT AVG(cod_sales) from jd_pachong_data_1 where cod_fenlei = '"+fenlei_data+"';"
	jd_cod_sales_avg_val = man_db.execute_seles(jd_cod_sales_avg)
	jd_sales_avg_val = int(jd_cod_sales_avg_val[0][0])
	print("京东平均销量 : ")
	print(jd_sales_avg_val)
	#淘宝天猫平均销量
	tbtm_cod_sales_avg = "SELECT AVG(cod_sales) from tb_tm_pachong_data_1 where cod_fenlei = '"+fenlei_data+"';"
	tbtm_cod_sales_avg_val = man_db.execute_seles(tbtm_cod_sales_avg)

	tbtm_sales_avg_val = int(tbtm_cod_sales_avg_val[0][0])
	print("淘宝天猫平均销量 : ")
	print(tbtm_sales_avg_val)
	#苏宁平均销量
	sn_cod_sales_avg = "SELECT AVG(cod_sales) from sn_pachong_data_1 where cod_fenlei = '"+fenlei_data+"';"
	sn_cod_sales_avg_val = man_db.execute_seles(sn_cod_sales_avg)
	sn_sales_avg_val = int(sn_cod_sales_avg_val[0][0])
	print("苏宁平均销量 : ")
	print(sn_sales_avg_val)
	all_sales_avg_val = int((jd_sales_avg_val+tbtm_sales_avg_val+sn_sales_avg_val)/3)
	print("平均销量值")
	print(all_sales_avg_val)
	return all_sales_avg_val

#all_cod_sales_avg('T恤')


#京东平均销量
def get_jd_sales_avg(fenlei_data):
	man_db = mandb.DB()
	#京东平均销量
	jd_cod_sales_avg = "SELECT AVG(cod_sales) from jd_pachong_data_1 where cod_fenlei = '"+fenlei_data+"';"
	jd_cod_sales_avg_val = man_db.execute_seles(jd_cod_sales_avg)
	jd_sales_avg_val = int(jd_cod_sales_avg_val[0][0])
	print("京东平均销量 : ")
	print(jd_sales_avg_val)
	return jd_sales_avg_val


#淘宝天猫平均销量
def get_tbtm_sales_avg(fenlei_data):
	man_db = mandb.DB()
	#淘宝天猫平均销量
	tbtm_cod_sales_avg = "SELECT AVG(cod_sales) from tb_tm_pachong_data_1 where cod_fenlei = '"+fenlei_data+"';"
	tbtm_cod_sales_avg_val = man_db.execute_seles(tbtm_cod_sales_avg)
	tbtm_sales_avg_val = int(tbtm_cod_sales_avg_val[0][0])
	print("淘宝天猫平均销量 : ")
	print(tbtm_sales_avg_val)
	return tbtm_sales_avg_val


#苏宁平均销量
def get_sn_sales_avg(fenlei_data):
	man_db = mandb.DB()
	#苏宁平均销量
	sn_cod_sales_avg = "SELECT AVG(cod_sales) from sn_pachong_data_1 where cod_fenlei = '"+fenlei_data+"';"
	sn_cod_sales_avg_val = man_db.execute_seles(sn_cod_sales_avg)
	sn_sales_avg_val = int(sn_cod_sales_avg_val[0][0])
	print("苏宁平均销量 : ")
	print(sn_sales_avg_val)
	return sn_sales_avg_val


#整体价格平均值
def all_cod_price_avg(fenlei_data):
	man_db = mandb.DB()
	#京东平均价格
	jd_cod_price_avg = "SELECT AVG(cod_price_1) from jd_pachong_data_1 where cod_fenlei = '"+fenlei_data+"';"
	jd_cod_price_avg_val = man_db.execute_seles(jd_cod_price_avg)
	jd_price_avg_val = int(jd_cod_price_avg_val[0][0])
	print("京东平均价格 : ")
	print(jd_price_avg_val)
	#淘宝天猫平均价格
	tbtm_cod_price_avg = "SELECT AVG(cod_price_2)/100  from tb_tm_pachong_data_1 where cod_fenlei = '"+fenlei_data+"';"
	tbtm_cod_price_avg_val = man_db.execute_seles(tbtm_cod_price_avg)
	tbtm_price_avg_val = int(tbtm_cod_price_avg_val[0][0])
	print("淘宝天猫平均价格 : ")
	print(tbtm_price_avg_val)
	#苏宁平均价格
	sn_cod_price_avg = "SELECT AVG(cod_price_1) from sn_pachong_data_1 where cod_fenlei = '"+fenlei_data+"';"
	sn_cod_price_avg_val = man_db.execute_seles(sn_cod_price_avg)
	sn_price_avg_val = int(sn_cod_price_avg_val[0][0])
	print("苏宁平均价格 : ")
	print(sn_price_avg_val)
	all_price_avg_val = int((jd_price_avg_val+tbtm_price_avg_val+sn_price_avg_val)/3)
	print("平均价格值")
	print(all_price_avg_val)
	return all_price_avg_val

#all_cod_price_avg('T恤')



#京东平均价格
def get_jd_price_avg(fenlei_data):
	man_db = mandb.DB()
	#京东平均价格
	jd_cod_price_avg = "SELECT AVG(cod_price_1) from jd_pachong_data_1 where cod_fenlei = '"+fenlei_data+"';"
	jd_cod_price_avg_val = man_db.execute_seles(jd_cod_price_avg)
	jd_price_avg_val = int(jd_cod_price_avg_val[0][0])
	print("京东平均价格 : ")
	print(jd_price_avg_val)
	return jd_price_avg_val


#淘宝天猫平均价格
def get_tmtb_price_avg(fenlei_data):
	man_db = mandb.DB()
	#淘宝天猫平均价格
	tbtm_cod_price_avg = "SELECT AVG(cod_price_2)/100  from tb_tm_pachong_data_1 where cod_fenlei = '"+fenlei_data+"';"
	tbtm_cod_price_avg_val = man_db.execute_seles(tbtm_cod_price_avg)
	tbtm_price_avg_val = int(tbtm_cod_price_avg_val[0][0])
	print("淘宝天猫平均价格 : ")
	print(tbtm_price_avg_val)
	return tbtm_price_avg_val


#苏宁平均价格
def get_sn_price_avg(fenlei_data):
	man_db = mandb.DB()
	#苏宁平均价格
	sn_cod_price_avg = "SELECT AVG(cod_price_1) from sn_pachong_data_1 where cod_fenlei = '"+fenlei_data+"';"
	sn_cod_price_avg_val = man_db.execute_seles(sn_cod_price_avg)
	sn_price_avg_val = int(sn_cod_price_avg_val[0][0])
	print("苏宁平均价格 : ")
	print(sn_price_avg_val)
	return sn_price_avg_val


#all_sales_avg = 487#all_cod_sales_avg('T恤')
#all_price_avg = 176#all_cod_price_avg('T恤')


#推荐算法
# 销量/平均销量*0.7+价格平均值/价格*0.3
def tuijian_go(fenlei_data,all_sales_avg,all_price_avg):
	man_db = mandb.DB()
	#all_sales_avg = all_cod_sales_avg(fenlei_data)
	#all_price_avg = all_cod_price_avg(fenlei_data)
	print(all_sales_avg)
	print(all_price_avg)
	#获取数据

	#get_datas = "SELECT * from jd_pachong_data_1 where cod_fenlei = '"+fenlei_data+"';"
	#get_datas = "SELECT * from sn_pachong_data_1 where cod_fenlei = '"+fenlei_data+"';"
	get_datas = "SELECT * from tb_tm_pachong_data_1 where cod_fenlei = '"+fenlei_data+"' AND cod_price_2 != 'isNull';"
	get_datas_val = man_db.execute_seles(get_datas)

	#print(get_datas_val)
	
	for datainfo in get_datas_val:
		try:
			print(datainfo)
			cod_sales_val = int(datainfo[8])
			#京东苏宁
			#cod_sales_val = int(datainfo[8])
			#京东苏宁
			#cod_price_val = int(float(datainfo[6]))
			#淘宝天猫
			cod_sales_val = int(datainfo[9])
			cod_price_val = int(float(datainfo[8]))
			#print(all_sales_avg)
			#print(all_price_avg)
			#print(cod_sales_val)
			#print(cod_price_val)
			#print(all_sales_avg)
			#print(all_price_avg)
			#print(cod_sales_val)
			#print(cod_price_val)
			if all_sales_avg == 0 or all_price_avg == 0:
				tuijian_val = 0
			else:
				tuijian_val = cod_sales_val/all_sales_avg*0.7+cod_price_val/all_price_avg*0.3
			#print("\n")
			#print(tuijian_val)
			if tuijian_val>1 and cod_sales_val>1000:
				
				
				print("数据：")
				
				#print(datainfo[0])
				#id_val = "jd_"+str(datainfo[0])
				id_val = "tbtm_"+str(datainfo[0])
				print("id_val = "+id_val)

				tj_fen = str(tuijian_val)
				print("tj_fen = "+tj_fen)

				#print(datainfo[1])
				fenlei_val = datainfo[1]
				print("fenlei_val = "+fenlei_val)

				#print(datainfo[2])
				name_val = datainfo[2]
				print("name_val = "+name_val)

				#print(datainfo[3])
				link_val = datainfo[3]
				print("link_val = "+link_val)

				#print(datainfo[4])
				pingtai_val = datainfo[4]
				print("pingtai_val = "+pingtai_val)

				#print(datainfo[5])
				price1_val = datainfo[7]
				print("price1_val = "+price1_val)
				#print(datainfo[6])
				price2_val = datainfo[8]
				print("price2_val = "+price2_val)
				
				#print(datainfo[7])
				img_val = datainfo[6]
				print("img_val = "+img_val)

				#print(datainfo[8])
				sales_val = datainfo[9]
				print("sales_val = "+sales_val)

				#print(datainfo[9])
				shopname_val = datainfo[10]
				print("shopname_val = "+shopname_val)
				add_tj(id_val, 
						tj_fen, 
						fenlei_val, 
						name_val, 
						link_val, 
						pingtai_val, 
						price1_val, 
						price2_val, 
						img_val, 
						sales_val, 
						shopname_val)
			print("\n\n")
		except:
			print("数据类型存在空值")
		continue



def tuijian_go2(fenlei_data,all_sales_avg,all_price_avg):
	man_db = mandb.DB()
	#all_sales_avg = all_cod_sales_avg(fenlei_data)
	#all_price_avg = all_cod_price_avg(fenlei_data)
	print(all_sales_avg)
	print(all_price_avg)
	#获取数据

	#get_datas = "SELECT * from jd_pachong_data_1 where cod_fenlei = '"+fenlei_data+"';"
	#get_datas = "SELECT * from sn_pachong_data_1 where cod_fenlei = '"+fenlei_data+"';"
	get_datas = "SELECT * from tb_tm_pachong_data_1 where cod_fenlei = '"+fenlei_data+"' AND cod_price_2 != 'isNull';"
	get_datas_val = man_db.execute_seles(get_datas)

	#print(get_datas_val)
	
	for datainfo in get_datas_val:
			print(datainfo)
			#京东苏宁
			#cod_sales_val = int(datainfo[8])
			#京东苏宁
			#cod_price_val = int(float(datainfo[6]))
			#淘宝天猫
			cod_sales_val = int(datainfo[9])
			cod_price_val = int(float(datainfo[8]))
			#print(all_sales_avg)
			#print(all_price_avg)
			#print(cod_sales_val)
			#print(cod_price_val)
			if all_sales_avg == 0 or all_price_avg == 0:
				tuijian_val = 0
			else:
				tuijian_val = cod_sales_val/all_sales_avg*0.7+cod_price_val/all_price_avg*0.3
			#print("\n")
			#print(tuijian_val)
			if tuijian_val>1 and cod_sales_val>1000:
				
				
				print("数据：")
				
				#print(datainfo[0])
				#id_val = "jd_"+str(datainfo[0])
				id_val = "tbtm_"+str(datainfo[0])
				print("id_val = "+id_val)

				tj_fen = str(tuijian_val)
				print("tj_fen = "+tj_fen)

				#print(datainfo[1])
				fenlei_val = datainfo[1]
				print("fenlei_val = "+fenlei_val)

				#print(datainfo[2])
				name_val = datainfo[2]
				print("name_val = "+name_val)

				#print(datainfo[3])
				link_val = datainfo[3]
				print("link_val = "+link_val)

				#print(datainfo[4])
				pingtai_val = datainfo[4]
				print("pingtai_val = "+pingtai_val)

				#print(datainfo[5])
				price1_val = datainfo[7]
				print("price1_val = "+price1_val)
				#print(datainfo[6])
				price2_val = datainfo[8]
				print("price2_val = "+price2_val)
				
				#print(datainfo[7])
				img_val = datainfo[6]
				print("img_val = "+img_val)

				#print(datainfo[8])
				sales_val = datainfo[9]
				print("sales_val = "+sales_val)

				#print(datainfo[9])
				shopname_val = datainfo[10]
				print("shopname_val = "+shopname_val)
				add_tj(id_val, 
						tj_fen, 
						fenlei_val, 
						name_val, 
						link_val, 
						pingtai_val, 
						price1_val, 
						price2_val, 
						img_val, 
						sales_val, 
						shopname_val)
			print("\n\n")


'''
cod_id char(30) NOT NULL,/* 商品id */
	tj_fen char(20) NOT NULL,/* 推荐分 */
	cod_fenlei char(30) NOT NULL,/* 商品分类 */
	cod_name char(100) NOT NULL,/* 商品名称 */
	cod_link VARCHAR(5000) NOT NULL,/* 商品链接 */
	pingtai char(15) NOT NULL,
	cod_price_1 char(50) NOT NULL,/* 实际价格 */
	cod_price_2 char(50) NOT NULL,/* 活动价格 */
	title_img_link VARCHAR(2000) NOT NULL,/* 图片 */
	cod_sales char(30) NOT NULL,/* 商品销量/评价 */
	cod_shop_name char(50) NOT NULL,/* 店家名称 */
	

'''


#插入推荐商品数据到推荐表
def add_tj(id_val, tj_fen, fenlei_val, name_val, link_val, pingtai_val, price1_val, price2_val, img_val, sales_val, shopname_val):
	man_db = mandb.DB()
	fenlei_val = fenlei_val.replace("\'","\'\'")
	name_val = name_val.replace("\'","")
	shopname_val = shopname_val.replace("\'","")
	sql = "INSERT INTO \
	tj_info_2 ( cod_id, tj_fen, cod_fenlei, cod_name, cod_link, pingtai, cod_price_1, cod_price_2, title_img_link, cod_sales, cod_shop_name) \
	VALUES ( '"+id_val+"', '"+tj_fen+"', '"+fenlei_val+"', '"+name_val+"', '"+link_val+"', '"+pingtai_val+"', '"+price1_val+"', \
	'"+price2_val+"', '"+img_val+"', '"+sales_val+"', '"+shopname_val+"');"
	man_db.execute(sql)


def get_count_cod():

	man_db = mandb.DB()
	#京东
	'''
	get_jd_fenlei = "SELECT cod_fenlei FROM jd_pachong_data_1 GROUP BY cod_fenlei;"
	get_jd_fenlei_val = man_db.execute_seles(get_jd_fenlei)
	print(get_jd_fenlei_val)
	'''

	'''
	get_tbtm_fenlei = "SELECT cod_fenlei FROM tbtm_pachong_data_1 GROUP BY cod_fenlei;"
	get_tbtm_fenlei_val = man_db.execute_seles(get_tbtm_fenlei)
	print(get_tbtm_fenlei_val)
	'''

	#苏宁
	'''
	get_sn_fenlei = "SELECT cod_fenlei FROM sn_pachong_data_1 GROUP BY cod_fenlei;"
	get_sn_fenlei_val = man_db.execute_seles(get_sn_fenlei)
	print(get_sn_fenlei_val)
	print("\n\n")

	get_sn_fenlei_have = 'SELECT cod_fenlei FROM tj_info_1 WHERE pingtai = "苏宁易购" GROUP BY cod_fenlei ;'
	get_sn_fenlei_have_val = man_db.execute_seles(get_sn_fenlei_have)
	print(get_sn_fenlei_have_val)
	
	print("\n\n")
	print(set(get_sn_fenlei_val) - set(get_sn_fenlei_have_val))

	get_sn_fenlei_vals = set(get_sn_fenlei_val) - set(get_sn_fenlei_have_val)
	'''
	
	#淘宝
	get_tmtb_fenlei = "SELECT cod_fenlei FROM tb_tm_pachong_data_1 GROUP BY cod_fenlei;"
	get_tmtb_fenlei_val = man_db.execute_seles(get_tmtb_fenlei)
	#print(get_tmtb_fenlei_val)

	get_tmtb_fenlei_have = 'SELECT cod_fenlei FROM tj_info_1 WHERE pingtai = "淘宝" or pingtai = "天猫" GROUP BY cod_fenlei ;'
	get_tmtb_fenlei_have_val = man_db.execute_seles(get_tmtb_fenlei_have)
	#print(get_tmtb_fenlei_have_val)

	get_tmtb_fenlei_vals = set(get_tmtb_fenlei_val) - set(get_tmtb_fenlei_have_val)
	
	print(get_tmtb_fenlei_vals)


	
	
	for keys in get_tmtb_fenlei_vals:
		print(keys[0])
		fenlei_data = keys[0]
		fenlei_data = fenlei_data.replace("\'","\'\'")
		#all_sales_avg = get_jd_sales_avg(fenlei_data)
		#all_sales_avg = get_sn_sales_avg(fenlei_data)
		#all_price_avg = get_jd_price_avg(fenlei_data)
		#all_price_avg = get_sn_price_avg(fenlei_data)

		all_sales_avg = get_tbtm_sales_avg(fenlei_data)
		all_price_avg = get_tmtb_price_avg(fenlei_data)

		print(all_sales_avg)
		print(all_price_avg)
		tuijian_go2(fenlei_data,all_sales_avg,all_price_avg)
	
	
#get_count_cod()



'''
number = 0
while number<100:
	tuijian_go('T恤',number,all_sales_avg,all_price_avg)
	number+=1
'''


#jd当前所有分类
# SELECT cod_fenlei FROM jd_pachong_data_1 GROUP BY cod_fenlei;


#添加到分类表
def add_fenlei_table(fenlei_val):
	man_db = mandb.DB()
	sql = "INSERT INTO codfenlei \
	( fenlei_val) \
	VALUES ( '"+fenlei_val+"');"
	man_db.execute(sql)
	print("[DeBug] add_fenlei_table")
	return 0


#获取分类
def get_fenlei():
	man_db = mandb.DB()



	get_tmtb_fenlei = "SELECT cod_fenlei FROM tb_tm_pachong_data_1 GROUP BY cod_fenlei;"
	get_tmtb_fenlei_val = man_db.execute_seles(get_tmtb_fenlei)

	#print(get_tmtb_fenlei_val)

	get_sn_fenlei = "SELECT cod_fenlei FROM sn_pachong_data_1 GROUP BY cod_fenlei;"
	get_sn_fenlei_val = man_db.execute_seles(get_sn_fenlei)

	#print(get_sn_fenlei_val)

	get_jd_fenlei = "SELECT cod_fenlei FROM jd_pachong_data_1 GROUP BY cod_fenlei;"
	get_jd_fenlei_val = man_db.execute_seles(get_jd_fenlei)

	#print(get_jd_fenlei_val)

	all_fenlei = set(get_tmtb_fenlei_val+get_sn_fenlei_val+get_jd_fenlei_val)

	for fenlei in  all_fenlei:
		print(fenlei)
		#添加到分类表
		add_fenlei_table(fenlei[0])

get_fenlei()